<?php

class YouNet_Model_DbTable_Visitors extends YouNet_Db_TableReport
{
	const TABLE_NAME = 'visitors';
    const STATISTIC_KEY = 'view';
	
	const TOTAL_VISITOR_NAME = "Total Visitors: ";
	const TOTAL_OLDFAN_NAME = "Total Old Fans: ";
	const TOTAL_OLDFAN_KEY = "total_old_fan";
	const TOTAL_NONFAN_NAME = "Total Non Fans: ";
	const TOTAL_NONFAN_KEY = "total_non_fan";
	
	protected $_name = self::TABLE_NAME;
	protected $_rowClass = 'YouNet_Model_Visitor';
	
	public static function recordVisitor($params)
	{
		$instance = new self();
		$query = "INSERT DELAYED INTO ". self::TABLE_NAME . " ("; 
		$col = $val = array();
		foreach ($params as $id => $key)
		{
			if (!is_null($params[$id]))
			{
				$col[] = $id;
				$val[] = $key;
			}
		}
		$query .= implode(',', $col) . ") VALUES ('";
		$query .= implode("','", $val) . "')";
		$instance->getAdapter()->query($query);
	}
    
	public static function getVisitorViewStatistic($id_campaign, $id_pages, $params)
	{
		$key = Core_Cache_IKeys::STATISTIC_REPORT_VIEW_TRAFFIC . $id_campaign . "_" . $params['fromDate'] . "_" . $params['toDate'];
		$views = YouNet_Util::getObjectMemCache()->load($key);
		if ($views === false)
		{
			$instance = new self();
			$select = $instance->select()
					->setIntegrityCheck(false)
					->from(array('p' => self::TABLE_NAME), array(
						'number'	=> new Zend_Db_Expr('count(*)'),
						'date'		=> new Zend_Db_Expr('DATE(FROM_UNIXTIME(p.time_visited))')		
					 ));
			if (isset($params['fromDate']))
				$select->where('time_visited >= ?', $params['fromDate']);
			if (isset($params['toDate']))
				$select->where('time_visited <= ?', $params['toDate'] + 24*60*60);
			$select->where('id_page IN (?)', $id_pages);
			$select->group('date');
			$data = $instance->fetchAll($select)->toArray();
		
			$likArr = array();
			$default = YouNet_Db_TableReport::getDefaultDateRangeArray($params['fromDate'], $params['toDate']);
			foreach ($data as $v)
				$likArr[$v['date']] = intval($v['number'], 10) ;
			$likArr = array_merge($default, $likArr); 

			$views = array(
				'younet_id_key'	=> self::STATISTIC_KEY,
				'name'	=> self::TOTAL_VISITOR_NAME,
				'color' => '#1E98CC',
				'data'	=> array_values($likArr)
			);
			YouNet_Util::getObjectMemCache()->save($views, $key, array(), REPORT_CACHE_TIME);
		}
		return $views;
		
	}
	public static function getVisitorViewPartitionStatistic($id_campaign, $id_pages, $params)
	{
		$key = Core_Cache_IKeys::STATISTIC_REPORT_VIEW_PARTITION_TRAFFIC . $id_campaign . "_" . $params['fromDate'] . "_" . $params['toDate'];
		$result = YouNet_Util::getObjectMemCache()->load($key);
		if ($result === false)
		{
			$instance = new self();
			$select = $instance->select()
					->setIntegrityCheck(false)
					->from(array('p' => self::TABLE_NAME), array(
						'non_fan'	=> new Zend_Db_Expr("COUNT( CASE WHEN is_old_fan = 0 THEN 'non_fan' END)"),
						'fan'	=> new Zend_Db_Expr("COUNT( CASE WHEN is_old_fan = 1 THEN 'fan' END)"),
						'date'		=> new Zend_Db_Expr('DATE(FROM_UNIXTIME(p.time_visited))')		
					 ));
			if (isset($params['fromDate']))
				$select->where('time_visited >= ?', $params['fromDate']);
			if (isset($params['toDate']))
				$select->where('time_visited <= ?', $params['toDate'] + 24*60*60);
			if (isset($id_campaign))
				$select->where('id_campaign = ?', $id_campaign);
			else
				$select->where('id_page IN (?)', $id_pages);
			$select->group('date');
			$views = $instance->fetchAll($select)->toArray();
			
			$totalFan = $nonFan = $fan = array();
			foreach ($views as $v)
			{
				$totalFan[$v['date']] = intval($v['non_fan'],10) + intval($v['fan'],10);
				$nonFan[$v['date']] = intval($v['non_fan'],10);
				$fan[$v['date']] = intval($v['fan'],10);
			}
			
			$default = $instance->getDefaultDateRangeArray($params['fromDate'], $params['toDate']);
			$totalFan = array_values(array_merge($default, $totalFan)); 
			$nonFan = array_values(array_merge($default, $nonFan)); 
			$fan = array_values(array_merge($default, $fan)); 
			$result = array( 
				array(
					'younet_id_key'	=> self::STATISTIC_KEY,
					'color' => '#1E98CC',
					'name'	=> self::TOTAL_VISITOR_NAME,
					'data'	=> $totalFan), 
				array(
					'younet_id_key'	=> self::TOTAL_NONFAN_KEY,
					'color' => '#ED7E17',
					'name'	=> self::TOTAL_NONFAN_NAME,
					'data'	=> $nonFan), 
				array(
					'younet_id_key'	=> self::TOTAL_OLDFAN_KEY,
					'color' => '#410049',
					'name'	=> self::TOTAL_OLDFAN_NAME,
					'data'	=> $fan)
			);
			YouNet_Util::getObjectMemCache()->save($result, $key, array(), REPORT_CACHE_TIME);
		}
		return $result;
	}
	
	public static function getVisitorDetailStatistic($id_campaign, $id_pages, $params)
	{
		$key = Core_Cache_IKeys::STATISTIC_REPORT_VIEW_DEMOGRAPHIC . $id_campaign . "_" . $params['fromDate'] . "_" . $params['toDate'];
		$result = YouNet_Util::getObjectMemCache()->load($key);
		if ($result === false)
		{
			$instance = new self();
			$ageRange  = YouNet_Db_TableReport::getAgeRange();
			$final = array();
			$test = "COUNT( CASE WHEN ";
			foreach($ageRange as $r)
			{
				$when = $then = '';
				$when .= "min_age >= ".$r['min'] . " ";
				$then .= "'" . $r['min'];
				if (isset($r['max']))
				{
					$when .= "AND min_age <= " . $r['max'] . " ";
					$then .= "-" . $r['max'];
				}
				else
					$then .= "+";
				$then  .= "' END) AS" . $then . "'";
				$when .= "THEN " . $then;
				$final[] = $test. $when;
			}
			$case = implode(", ", $final);
			$select = $instance->select()
					->setIntegrityCheck(false)
					->from(self::TABLE_NAME, array(new Zend_Db_Expr($case)
					 ))
					->where('time_visited <= ?', $params['toDate'] + 24*60*60)
					->where('time_visited >= ?', $params['fromDate']);
			if (isset($id_campaign))
				$select->where('id_campaign = ?', $id_campaign);
			else
				$select->where('id_page IN (?)', $id_pages);
			$ageGenderData = $instance->fetchRow($select)->toArray();
			
			$range = $data = array();
			foreach ($ageGenderData as $id => $val)
			{
				$range[] = $id;
				$data[] = intval($val,10);
			}
			$result = array($range, array(
				array(
					'name'	=>	'Age',
					'data'	=>	$data
				)));
			YouNet_Util::getObjectMemCache()->save($result, $key, array(), REPORT_CACHE_TIME);
		}
		return $result;
	}
}
